# 竞赛dao层
from dao import db


# 查询是否报名该竞赛
def dao_race_isSignUp(raceId, uuid):
    sql = "select * from raceScore where raceId = %d and uuid = %d" % (raceId, uuid)
    data = db.session.execute(sql).fetchone()
    if data is not None:
        return True
    else:
        return False


# 查询竞赛Tag
def dao_race_searchTag(raceId):
    sql = "select tag from raceTag where raceId = %d" % raceId
    data = db.session.execute(sql).fetchall()
    if data is None:
        return []
    else:
        result = []
        for temp in data:
            field_li = ['tag']
            t_result = dict(zip(field_li, temp))
            result.append(t_result['tag'])
        return result


# 查询竞赛排名/总
def dao_race_searchRank(raceId, uuid):
    sql = "select uuid from raceScore where raceId = %d and handle = TRUE ORDER BY score desc" \
          % raceId
    data = db.session.execute(sql).fetchall()
    if data is None:
        return False
    else:
        temp_result = []
        for temp in data:
            field_li = ['uuid']
            t_result = dict(zip(field_li, temp))
            temp_result.append(t_result['uuid'])
        if uuid not in temp_result:
            result = str(len(temp_result)) + '/0'
        else:
            result = str(len(temp_result)) + '/' + str(temp_result.index(uuid))
        return result


# 查询竞赛排名
def dao_race_rank_searchRank(raceId, uuid):
    sql = "select uuid from raceScore where raceId = %d and handle = TRUE ORDER BY score desc" \
          % raceId
    data = db.session.execute(sql).fetchall()
    if data is None:
        return False
    else:
        temp_result = []
        for temp in data:
            field_li = ['uuid']
            t_result = dict(zip(field_li, temp))
            temp_result.append(t_result['uuid'])
        result = str(temp_result.index(uuid))
        return result


# 查询竞赛是否有该标签
def dao_race_hasTag(raceId, tag):
    sql = "select * from raceTag where raceId = %d and tag = '%s'" % (raceId, tag)
    data = db.session.execute(sql).fetchone()
    if data is not None:
        return True
    else:
        return False


#  数据库获取竞赛列表详情
def dao_race_listDetail(raceId):
    sql_1 = "select raceId, previewImg, title, dateStart, dateEnd" \
            " from raceItem where raceId = %d" % raceId
    data_1 = db.session.execute(sql_1).fetchone()
    if data_1 is None:
        return False
    else:
        field_li_1 = ['raceId', 'previewImg', 'title', 'dateStart', 'dateEnd']
        result = dict(zip(field_li_1, data_1))
        result['dateStart'] = str(result['dateStart'])
        result['dateEnd'] = str(result['dateEnd'])
        result['tags'] = dao_race_searchTag(raceId)
        return result


#  数据库获取竞赛列表
def dao_race_getRaceList(status, category, raceCategory, search, uuid):
    # 竞赛状态,0为全部，1为已提交，2为未提交
    # 比赛类别，用来标签筛选用
    # 0为全部，1为训练赛，2为竞赛
    # 搜索内容,空则不进行搜索返回全部内容
    if status == 0:
        if raceCategory == 0:
            sql = "select raceId, title from raceItem"
        elif raceCategory in (1, 2):
            sql = "select raceId, title from raceItem where raceCategory = %d" % raceCategory
        else:
            return False
    elif status == 1:
        if raceCategory == 0:
            sql = "select raceId, title from raceItem where raceId in " \
                  "(select raceId from raceScore where uuid = %d and handle = TRUE)" % uuid
        elif raceCategory in (1, 2):
            sql = "select raceId, title from raceItem where raceCategory = %d and raceId in " \
                  "(select raceId from raceScore where uuid = %d and handle = TRUE)" % (raceCategory, uuid)
        else:
            return False
    elif status == 2:
        if raceCategory == 0:
            sql = "select raceId, title from raceItem where raceId not in " \
                  "(select raceId from raceScore where uuid = %d and handle = TRUE)" % uuid
        elif raceCategory in (1, 2):
            sql = "select raceId, title from raceItem where raceCategory = %d and raceId not in " \
                  "(select raceId from raceScore where uuid = %d and handle = TRUE)" % (raceCategory, uuid)
        else:
            return False
    else:
        return False

    data = db.session.execute(sql).fetchall()
    if data is None:
        return False
    else:
        result = []
        for temp in data:
            field_li = ['raceId', 'title']
            t_result = dict(zip(field_li, temp))
            flag = False
            if category:
                for t in category:
                    if dao_race_hasTag(t_result['raceId'], t):
                        flag = True
                        break
                    else:
                        continue
                if flag:
                    if search is None:
                        temp_result = dao_race_listDetail(t_result['raceId'])
                        result.append(temp_result)
                    elif search in t_result['title']:
                        temp_result = dao_race_listDetail(t_result['raceId'])
                        result.append(temp_result)
                    else:
                        continue
                else:
                    continue
            else:
                if search is None:
                    temp_result = dao_race_listDetail(t_result['raceId'])
                    result.append(temp_result)
                elif search in t_result['title']:
                    temp_result = dao_race_listDetail(t_result['raceId'])
                    result.append(temp_result)
                else:
                    continue

    return result


#  数据库获取竞赛详情
def dao_race_detail(raceId, uuid):
    sql_1 = "select title, previewImg, dateStart, dateEnd, content" \
            " from raceItem where raceId = %d" % raceId
    data_1 = db.session.execute(sql_1).fetchone()
    if data_1 is None:
        return False
    else:
        field_li_1 = ['title', 'previewImg', 'dateStart', 'dateEnd', 'content']
        result = dict(zip(field_li_1, data_1))
        result['dateStart'] = str(result['dateStart'])
        result['dateEnd'] = str(result['dateEnd'])
        result['tags'] = dao_race_searchTag(raceId)
        result['myrank'] = dao_race_searchRank(raceId, uuid)
        return result


# 查询用户名
def dao_race_searchUserName(uuid):
    sql = "select userName from user where uuid = %d" % uuid
    data = db.session.execute(sql).fetchone()
    if data is None:
        return {'userName': None}
    else:
        field_li = ['userName']
        result = dict(zip(field_li, data))
        return result['userName']


#  数据库获取竞赛排名
def dao_race_rank(raceId):
    sql = "select uuid, handleDate from raceScore where raceId = %d and handle = TRUE ORDER BY score desc" \
          % raceId
    data = db.session.execute(sql).fetchall()
    if data is None:
        return False
    else:
        result = []
        for temp in data:
            field_li = ['uuid', 'handleDate']
            t_result = dict(zip(field_li, temp))
            temp_result = {
                'date': str(t_result['handleDate']),
                'userName': dao_race_searchUserName(t_result['uuid']),
                'rank': dao_race_rank_searchRank(raceId, t_result['uuid'])
            }
            result.append(temp_result)
        return result


#  提交比赛成绩
def dao_race_submitFile(raceId, uuid, score):
    if not dao_race_isSignUp(raceId, uuid):
        return False

    sql = "update raceScore set score = %f, handleDate = now(), handle = TRUE where raceId = %d and uuid = %d" \
          % (score, raceId, uuid)
    try:
        db.session.execute(sql)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        return True
